1 Einleitung

Die Daten stammen aus dem FIS-Broker der Senatsverwaltung für Stadtentwicklung und Wohnen. Für die Jahre 2009 und 2010 fehlen aktuell die Attribute.

Zur Arbeit mit dem FIS-Broker siehe auch den Vortrag “Daten aus dem FIS-Broker” von Jochen Klar zum ODD 2017 in Berlin.

2 Setup

library("gdalUtils")
library("sp")
library("ggplot2")
suppressPackageStartupMessages(library("rgdal"))
suppressPackageStartupMessages(library("dplyr"))
library("leaflet")
library("viridis")

knitr::opts_chunk$set(echo = TRUE)
read_brw <- function(year) {

  dsn  <- paste0("WFS:http://fbinter.stadt-berlin.de/fb/wfs/geometry/senstadt/re_brw", year)
  dest <- paste0("./data/brw", year, ".shp")
  fis  <- paste0("fis:re_brw", year)
   
  ogr2ogr(s_srs = "EPSG:25833", 
          t_srs = "WGS84", 
          f = "ESRI Shapefile", 
          dsn, 
          dest, 
          fis,
          overwrite = TRUE)

}

read_shp <- function(x) {

  a1 <- paste0("./data/brw", x, ".shp")
  a2 <- paste0("brw", x)
  z <- readOGR(a1, a2, stringsAsFactors = FALSE)
  z@data
}

3 Download der WFS Datensätze

Map(read_brw, 2002:2017)

4 Import der Attributdaten

dat <- Map(read_shp, 2002:2017) %>%
       bind_rows() %>%
       readr::type_convert() %>%
       mutate(year = as.numeric(format(STICHTAG, "%Y"))) %>%
       arrange(spatial_na, year)
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2002.shp", layer: "brw2002"
## with 1035 features
## It has 11 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2003.shp", layer: "brw2003"
## with 1036 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2004.shp", layer: "brw2004"
## with 1034 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2005.shp", layer: "brw2005"
## with 1038 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2006.shp", layer: "brw2006"
## with 1009 features
## It has 11 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2007.shp", layer: "brw2007"
## with 1062 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2008.shp", layer: "brw2008"
## with 1060 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2009.shp", layer: "brw2009"
## with 1071 features
## It has 4 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2010.shp", layer: "brw2010"
## with 1088 features
## It has 4 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2011.shp", layer: "brw2011"
## with 1093 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2012.shp", layer: "brw2012"
## with 1097 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2013.shp", layer: "brw2013"
## with 1153 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2014.shp", layer: "brw2014"
## with 1161 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2015.shp", layer: "brw2015"
## with 1145 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2016.shp", layer: "brw2016"
## with 1136 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al 
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2017.shp", layer: "brw2017"
## with 1142 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al
## Parsed with column specification:
## cols(
##   gml_id = col_character(),
##   spatial_na = col_integer(),
##   spatial_al = col_integer(),
##   spatial_ty = col_character(),
##   BEZIRK = col_character(),
##   ANWERT = col_character(),
##   NUTZUNG = col_character(),
##   STICHTAG = col_datetime(format = ""),
##   BEITRAGSZU = col_character(),
##   LUMNUM = col_character(),
##   VERFAHRENS = col_character(),
##   GEMEINDE = col_character()
## )
glimpse(dat)
## Observations: 17,360
## Variables: 15
## $ gml_id     <chr> "re_brw2009.0", "re_brw2010.0", "re_brw2010.0", "re...
## $ spatial_na <int> 0, 0, 0, 0, 5, 1000, 1000, 1000, 1000, 1000, 1001, ...
## $ spatial_al <int> 0, 0, 0, 0, 5, 1000, 1000, 1000, 1000, 1000, 1001, ...
## $ spatial_ty <chr> "Polygon", "Polygon", "Polygon", "Polygon", "Polygo...
## $ BEZIRK     <chr> NA, NA, NA, NA, NA, "Marzahn-Hellersdorf", "Marzahn...
## $ ANWERT     <chr> NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", NA, NA...
## $ BRW        <dbl> NA, NA, NA, NA, NA, 160, 140, 120, 100, 80, 180, 16...
## $ NUTZUNG    <chr> NA, NA, NA, NA, NA, "G - Gewerbe", "G - Gewerbe", "...
## $ STICHTAG   <dttm> NA, NA, NA, NA, NA, 2002-01-01, 2003-01-01, 2004-0...
## $ BEITRAGSZU <chr> NA, NA, NA, NA, NA, "Beitragsfrei nach BauGB", "Bei...
## $ LUMNUM     <chr> NA, NA, NA, NA, NA, "[[url]]", "[[url]]", "[[url]]"...
## $ VERFAHRENS <chr> NA, NA, NA, NA, NA, NA, "Entw", "Entw", "Entw", "En...
## $ GFZ        <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,...
## $ GEMEINDE   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,...
## $ year       <dbl> NA, NA, NA, NA, NA, 2002, 2003, 2004, 2005, 2007, 2...

5 Anzahl der Werte nach Bezirken

  table(dat$BEZIRK, dat$year) %>% knitr::kable()
2002 2003 2004 2005 2006 2007 2008 2011 2012 2013 2014 2015 2016 2017
Charlottenburg-Wilmersdorf 70 70 70 74 70 78 76 77 78 80 79 79 80 80
Friedrichshain-Kreuzberg 44 43 42 44 37 37 38 40 39 37 36 36 33 34
Lichtenberg 75 74 72 73 70 71 71 73 75 82 83 83 82 81
Marzahn-Hellersdorf 46 49 49 48 48 52 55 56 58 57 57 58 57 57
Mitte 92 92 93 91 80 84 89 91 92 93 96 92 92 95
Neukölln 59 59 59 61 60 60 60 62 63 64 64 64 64 64
Pankow 122 122 121 122 121 112 116 124 121 129 131 129 126 125
Reinickendorf 95 95 93 92 92 97 96 100 99 108 106 104 103 104
Spandau 122 122 125 124 122 125 129 137 139 147 148 149 149 147
Steglitz-Zehlendorf 112 112 114 114 106 113 114 122 121 125 126 125 125 127
Tempelhof-Schöneberg 69 69 68 68 61 69 69 70 70 73 74 72 73 74
Treptow-Köpenick 128 129 128 127 131 131 135 141 142 158 161 154 152 154

6 Anzahl der Werte nach Art der Nutzung

 table(dat$NUTZUNG, dat$year) %>% knitr::kable()
2002 2003 2004 2005 2006 2007 2008 2011 2012 2013 2014 2015 2016 2017
F - Forstflächen 0 0 0 0 0 0 0 0 0 35 35 35 35 35
G - Gewerbe 234 234 234 228 234 242 243 238 238 236 235 227 224 223
GB - Gemeinbedarf 0 0 0 0 0 0 0 34 35 37 38 39 39 39
Gp - Gewerbe produzierend 18 18 17 18 15 10 9 7 6 4 4 4 4 4
LW-A - Acker 0 0 0 0 0 0 0 0 0 10 10 11 11 11
LW-EGA - Erwerbsgartenbau 0 0 0 0 0 0 0 0 0 4 4 3 3 3
LW-G - Grünland 0 0 0 0 0 0 0 0 0 6 0 0 0 0
LW-GR - Grünland 0 0 0 0 0 0 0 0 0 0 6 6 6 6
M1 - Kerngebiet 54 54 54 52 35 49 52 54 54 56 59 55 53 53
M1* - Einzelhandelszentrum 0 0 0 0 12 14 15 16 16 15 15 15 8 8
M2 - Mischgebiet 48 49 50 48 19 43 43 44 45 43 41 39 37 39
W - Wohngebiet 680 681 679 692 683 671 686 700 703 707 714 711 716 721
z <- dat %>% 
        group_by(BEZIRK, NUTZUNG, GFZ, year) %>% 
        summarise(count = n(),
                  q25 = quantile(BRW, 0.25, na.rm = TRUE),
                  q75 = quantile(BRW, 0.75, na.rm = TRUE),
                  q95 = quantile(BRW, 0.75, na.rm = TRUE),
                  iqr = IQR(BRW, na.rm = TRUE),
                  mean = mean(BRW, na.rm = TRUE),
                  med = median(BRW, na.rm = TRUE))  %>%
        filter(!is.na(BEZIRK)) %>%
        ungroup()

7 In der Nutuzng W - Wohngebiet

p0 <- ggplot(filter(z, year >= 2011, NUTZUNG == "W - Wohngebiet"),
       aes(year, mean, colour = "BEZIRK", group = "BEZIRK"))
p0 <- p0 + geom_line()
p0 <- p0 + facet_grid(as.factor(GFZ) ~ BEZIRK, scales = "free")
p0 <- p0 + theme(axis.text.x  = element_text(angle=90, vjust=0.5, size = 8),
                 axis.text.y  = element_text(size = 8), 
                 legend.position="none")
p0 <- p0 + labs(x = "",
                y = "Durchschnitt in  €",
                title = "Bodenrichtwerte in der Nutzung 'W - Wohngebiet' (2011-2017)",
                subtitle = "",
                caption = "Quelle: FIS-Broker / Gutachterausschuss für Grundstückswerte in Berlin")
p0

8 In der Nutzung M2 - Mischgebiet

p1 <- ggplot(filter(z, year >= 2011, NUTZUNG == "M2 - Mischgebiet"),
       aes(year, mean, colour = "BEZIRK", group = "BEZIRK"))
p1 <- p1 + geom_line()
p1 <- p1 + facet_grid(as.factor(GFZ) ~ BEZIRK, scales = "free")
p1 <- p1 + theme(axis.text.x  = element_text(angle=90, vjust=0.5, size = 8),
                 axis.text.y  = element_text(size = 8),
                 legend.position="none")
p1 <- p1 + labs(x = "",
                y = "Durchschnitt in €",
                title = "Bodenrichtwerte in der Nutzung 'M2 - Mischgebiet' (2011-2017)",
                subtitle = "",
                caption = "Quelle: FIS-Broker / Gutachterausschuss für Grundstückswerte in Berlin")
p1

9 Karte der Bodenrichtwerte im Jahr 2017 (BRW <= 8.000 €)

brw <- readOGR("./data/brw2017.shp", "brw2017", 
               stringsAsFactors = FALSE, 
               encoding = "UTF-8")
## OGR data source with driver: ESRI Shapefile 
## Source: "./data/brw2017.shp", layer: "brw2017"
## with 1142 features
## It has 12 fields
## Integer64 fields read as strings:  spatial_na spatial_al
brw <- brw[brw$BRW <= 8000, ]

pal <- colorNumeric(
  palette = viridis_pal()(10),
  domain = brw@data$BRW
)

popup <- paste0("<b>", brw@data$spatial_al, " - ",
                       brw@data$NUTZUNG , " - ",
                       brw@data$GFZ, " - ", 
                       brw@data$BRW, " Euro/qm")

leafMap <- leaflet(height = "800px", width = "800px") %>%
  setView(lng = 13.383, lat = 52.516, zoom = 11) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addPolygons(data = brw,
              stroke = TRUE,
              dashArray = 1,
              weight = 1.5,
              color = "white",
              smoothFactor = 0.20,
              fillOpacity = 0.60,
              fillColor = ~pal(brw@data$BRW),
              popup = popup,
              group = "Bodenrichtwerte") %>%
  addLegend("bottomright",
            pal = pal,
            values = brw@data$BRW,
            title = "Euro/qm",
            labFormat = labelFormat(suffix = " "),
            opacity = 1)
leafMap

10 Session Info

devtools::session_info()
## Session info --------------------------------------------------------------
##  setting  value                                      
##  version  R version 3.3.2 Patched (2016-12-01 r71715)
##  system   x86_64, mingw32                            
##  ui       RTerm                                      
##  language (EN)                                       
##  collate  German_Germany.1252                        
##  tz       Europe/Berlin                              
##  date     2017-03-07
## Packages ------------------------------------------------------------------
##  package     * version    date       source                               
##  assertthat    0.1        2013-12-06 CRAN (R 3.3.2)                       
##  backports     1.0.5      2017-01-18 CRAN (R 3.3.2)                       
##  codetools     0.2-15     2016-10-05 CRAN (R 3.3.2)                       
##  colorspace    1.3-2      2016-12-14 CRAN (R 3.3.2)                       
##  crosstalk     1.0.1      2017-01-26 Github (rstudio/crosstalk@6a36da1)   
##  DBI           0.5-1      2016-09-10 CRAN (R 3.3.2)                       
##  devtools      1.12.0     2016-06-24 CRAN (R 3.3.2)                       
##  digest        0.6.12     2017-01-27 CRAN (R 3.3.2)                       
##  dplyr       * 0.5.0      2016-06-24 CRAN (R 3.3.2)                       
##  evaluate      0.10       2016-10-11 CRAN (R 3.3.2)                       
##  foreach       1.4.3      2015-10-13 CRAN (R 3.3.2)                       
##  gdalUtils   * 2.0.1.7    2015-10-10 CRAN (R 3.3.2)                       
##  ggplot2     * 2.2.1.9000 2017-03-03 Github (tidyverse/ggplot2@fa8004e)   
##  gridExtra     2.2.1      2016-02-29 CRAN (R 3.3.2)                       
##  gtable        0.2.0      2016-02-26 CRAN (R 3.3.2)                       
##  highr         0.6        2016-05-09 CRAN (R 3.3.2)                       
##  hms           0.3        2016-11-22 CRAN (R 3.3.2)                       
##  htmltools     0.3.5      2016-03-21 CRAN (R 3.3.2)                       
##  htmlwidgets   0.8        2016-12-17 Github (ramnathv/htmlwidgets@3ce9f90)
##  httpuv        1.3.3      2015-08-04 CRAN (R 3.3.2)                       
##  iterators     1.0.8      2015-10-13 CRAN (R 3.3.2)                       
##  jsonlite      1.3        2017-02-28 CRAN (R 3.3.2)                       
##  knitr         1.15.1     2016-11-22 CRAN (R 3.3.2)                       
##  labeling      0.3        2014-08-23 CRAN (R 3.3.2)                       
##  lattice       0.20-34    2016-09-06 CRAN (R 3.3.2)                       
##  lazyeval      0.2.0.9000 2016-12-19 Github (hadley/lazyeval@c155c3d)     
##  leaflet     * 1.1.0      2017-02-21 CRAN (R 3.3.2)                       
##  magrittr      1.5        2014-11-22 CRAN (R 3.3.2)                       
##  memoise       1.0.0      2016-01-29 CRAN (R 3.3.2)                       
##  mime          0.5        2016-07-07 CRAN (R 3.3.2)                       
##  munsell       0.4.3      2016-02-13 CRAN (R 3.3.2)                       
##  plyr          1.8.4      2016-06-08 CRAN (R 3.3.2)                       
##  R.methodsS3   1.7.1      2016-02-16 CRAN (R 3.3.2)                       
##  R.oo          1.21.0     2016-11-01 CRAN (R 3.3.2)                       
##  R.utils       2.5.0      2016-11-07 CRAN (R 3.3.2)                       
##  R6            2.2.0      2016-10-05 CRAN (R 3.3.2)                       
##  raster        2.5-8      2016-06-02 CRAN (R 3.3.2)                       
##  Rcpp          0.12.9     2017-01-14 CRAN (R 3.3.2)                       
##  readr         1.0.0.9000 2017-02-12 Github (tidyverse/readr@5f9fd71)     
##  reshape2      1.4.2      2016-10-22 CRAN (R 3.3.2)                       
##  rgdal       * 1.2-5      2016-12-15 CRAN (R 3.3.2)                       
##  rmarkdown     1.3        2016-12-21 CRAN (R 3.3.2)                       
##  rprojroot     1.2        2017-01-16 CRAN (R 3.3.2)                       
##  scales        0.4.1      2016-11-09 CRAN (R 3.3.2)                       
##  shiny         1.0.0      2017-01-12 CRAN (R 3.3.2)                       
##  sp          * 1.2-4      2017-01-04 Github (edzer/sp@8507a7b)            
##  stringi       1.1.2      2016-10-01 CRAN (R 3.3.2)                       
##  stringr       1.2.0      2017-02-18 CRAN (R 3.3.2)                       
##  tibble        1.2        2016-08-26 CRAN (R 3.3.2)                       
##  viridis     * 0.3.4      2016-12-03 Github (sjmgarnier/viridis@e54d97f)  
##  withr         1.0.2      2016-06-20 CRAN (R 3.3.2)                       
##  xtable        1.8-2      2016-02-05 CRAN (R 3.3.2)                       
##  yaml          2.1.14     2016-11-12 CRAN (R 3.3.2)